<?php

declare(strict_types=1);

namespace App\CustHub\Mapper;

use App\CustHub\Model\ChChannelCode;
use App\CustHub\Model\ChChannelCodeRecord;
use Carbon\Carbon;
use Hyperf\Database\Query\Expression;
use Hyperf\DbConnection\Db;
use Mine\Abstracts\AbstractMapper;
use function Hyperf\Collection\collect;

class ChChannelCodeRecordMapper extends AbstractMapper
{
    public function assignModel()
    {
        $this->model = ChChannelCodeRecord::class;
    }

    public function getCodeStatisticsByUid($data)
    {
        $uid = $data['uid'];
        // 1. 处理时间参数
        $time = Carbon::now();

        // 2. 处理code_ids参数
        $codeIds = (array)($data['code_ids'] ?? []);
        $codeIds = array_filter(array_map('strval', $codeIds));

        // 3. 获取7天前的开始时间
        $sevenDaysAgo = $time->copy()->subDays(7)->startOfDay();

        // 4. 构建查询
        $query = $this->model::query()->where('uid', $uid);

        // 6. 执行查询
        $results = $query
            ->selectRaw('
            code_id,
            COUNT(*) as total_count,
            COUNT(CASE WHEN created_at >= ? THEN 1 END) as weekly_count,
            COUNT(CASE WHEN DATE(created_at) = ? THEN 1 END) as daily_count
        ', [$sevenDaysAgo, $time->toDateString()])
            ->when(!empty($codeIds), function ($q) use ($codeIds) {
                $q->whereIn('code_id', $codeIds);
            })
            ->groupBy('code_id')
            ->get()
            ->keyBy('code_id');

        return $results;
    }

    public function getCodeStatisticsByCodeIdAndCodeId(int $uid,string $codeId)
    {
        {
            // 1. 处理时间参数
            $time = Carbon::now();

            // 3. 获取7天前的开始时间
            $sevenDaysAgo = $time->copy()->subDays(7)->startOfDay();

            // 4. 构建查询
            $query = $this->model::query()->where('uid', $uid)->where('code_id', $codeId);

            // 6. 执行查询
            $results = $query
                ->selectRaw('
            COUNT(*) as total_count,
           COUNT(CASE WHEN created_at >=? THEN 1 END) as weekly_count, 
           COUNT(CASE WHEN DATE(created_at) =? THEN 1 END) as daily_count,
           COUNT(CASE WHEN is_remain = 1 THEN 1 END) as remained_count, 
           COUNT(CASE WHEN is_new = 1 THEN 1 END) as new_contact_count 
        ', [$sevenDaysAgo, $time->toDateString()])
                ->get();

            foreach ($results as $result) {
                $result['weekly_count'] = (int)$result['weekly_count'];
                $result['daily_count'] = (int)$result['daily_count'];
                $result['remained_count'] = (int)$result['remained_count'];
                $result['new_contact_count'] = (int)$result['new_contact_count'];
            }

            return $results;
        }
    }

    public function getDailyCodeStatisticsByUidAndCodeId(int $uid, string $codeId)
    {
        $firstDay = ChChannelCodeRecord::where('uid', $uid)
            ->selectRaw('MIN(created_at) as first_day')
            ->first()
            ->first_day;

        if (!$firstDay) {
            return $this->model::query()->whereRaw('1 = 0');
        }

        $firstDay = Carbon::parse($firstDay)->startOfDay();
        $today = Carbon::tomorrow()->startOfDay();

        // 修改点1：直接返回查询构建器
        return $this->model::selectRaw('
            DATE(created_at) AS date,
            COUNT(*) AS daily_count,
            COUNT(CASE WHEN is_remain = 1 THEN 1  END) AS remained_count
        ')
            ->where('uid', $uid)
            ->where('code_id', $codeId)
            ->where('created_at', '>=', $firstDay)
            ->where('created_at', '<=', $today)
            ->groupBy(new Expression('DATE(created_at)'))
            ->orderBy(new Expression('DATE(created_at)'), 'desc');
    }

    public function getRobotCodeStatisticsByUidAndCodeId($uid, string $codeId)
    {
        return $this->model::query()
            ->selectRaw('
            robot_id,
            COUNT(*) AS daily_count,
            COUNT(CASE WHEN is_remain = 1 THEN 1 END) AS remained_count
        ')
            ->where('ch_channel_code_record.uid', $uid)
            ->where('ch_channel_code_record.code_id', $codeId)
            ->groupBy('robot_id');
    }

    public function getChannelReport(int $uid, array $codeIds = [], int $timeType = 0, $channelName = null)
    {
        if (empty($codeIds)) {
            return $this->model::query()->where('1 = 0');
        }

        $now = Carbon::now();
        $isDaily = $timeType === 0;

        // 时间范围计算
        [$start, $end, $dateLabel] = match ($timeType) {
            1 => [
                $now->copy()->subDays(6)->startOfDay(),
                $now->copy()->endOfDay(),
                $now->copy()->subDays(6)->format('Y-m-d').'至'.$now->format('Y-m-d')
            ],
            2 => [
                $now->copy()->subDays(13)->startOfDay(),
                $now->copy()->endOfDay(),
                $now->copy()->subDays(13)->format('Y-m-d').'至'.$now->format('Y-m-d')
            ],
            3 => [
                $now->copy()->subDays(29)->startOfDay(),
                $now->copy()->endOfDay(),
                $now->copy()->subDays(29)->format('Y-m-d').'至'.$now->format('Y-m-d')
            ],
            default => [
                $now->copy()->startOfDay(),
                $now->copy()->endOfDay(),
                $now->format('Y-m-d')
            ]
        };

        return ChChannelCode::query()
            ->select([
                // 修复点：使用COALESCE处理null值
                $isDaily
                    ? DB::raw("COALESCE(DATE(cccr.created_at), '{$now->toDateString()}') as date_range")
                    : DB::raw("'{$dateLabel}' as date_range"),
                'ccc.channel_name',
                DB::raw('COUNT(cccr.id) as new_count'),
                DB::raw('COALESCE(SUM(CASE WHEN cccr.is_remain = 1 THEN 1 ELSE 0 END), 0) as remained_count')
            ])
            ->from('ch_channel_code as ccc')
            ->leftJoin('ch_channel_code_record as cccr', function ($join) use ($uid, $start, $end) {
                $join->on('ccc.id', '=', 'cccr.code_id')
                    ->where('cccr.uid', $uid)
                    ->whereBetween('cccr.created_at', [$start, $end]);
            })
            ->whereIn('ccc.id', $codeIds)
            ->when($channelName, function ($q, $channelName) {
                $q->where('ccc.channel_name', $channelName);
            })
            ->when($isDaily, function ($q) {
                // 修复点2：使用与select一致的表达式分组
                $q->groupBy(DB::raw('DATE(cccr.created_at)'), 'ccc.channel_name');
            }, function ($q) {
                $q->groupBy('ccc.channel_name');
            })
            ->orderByDesc('date_range');
    }

    public function getChannelReportDetail(int $uid, array $codeIds = [], int $timeType = 0, $channelName = null)
    {
        if (empty($codeIds)) {
            return collect();
        }

        $now = Carbon::now();

        // 1. 生成日期范围
        [$start, $end] = match ($timeType) {
            1 => [$now->copy()->subDays(6)->startOfDay(), $now->copy()->endOfDay()],  // 最近7天
            2 => [$now->copy()->subDays(13)->startOfDay(), $now->copy()->endOfDay()], // 最近14天
            3 => [$now->copy()->subDays(29)->startOfDay(), $now->copy()->endOfDay()], // 最近30天
            default => [$now->copy()->startOfDay(), $now->copy()->endOfDay()]         // 当天
        };

        // 2. 获取所有相关渠道名称
        $channels = DB::table('ch_channel_code')
            ->whereIn('id', $codeIds)
            ->when($channelName, function ($q, $channelName) {
                $q->where('channel_name', $channelName);
            })
            ->pluck('channel_name')
            ->unique()
            ->toArray();

        if (empty($channels))
        {
            return collect();
        }

        // 3. 获取实际数据（优化后的查询）
        $actualData = DB::table('ch_channel_code_record as cccr')
            ->select([
                'ccc.channel_name',
                DB::raw('DATE(cccr.created_at) as date'),
                DB::raw('COUNT(cccr.id) as total_count')
            ])
            ->join('ch_channel_code as ccc', 'cccr.code_id', '=', 'ccc.id') // 改用内连接提升性能
            ->where('cccr.uid', $uid)
            ->whereIn('cccr.code_id', $codeIds)
            ->whereBetween('cccr.created_at', [$start, $end])
            ->groupBy('date', 'ccc.channel_name')
            ->get()
            ->groupBy('channel_name'); // 首次按渠道分组

        // 4. 生成完整日期序列
        $dateRange = [];
        $currentDate = $start->copy();
        while ($currentDate <= $end) {
            $dateRange[] = $currentDate->format('Y-m-d');
            $currentDate->addDay();
        }

        // 5. 构建结构化数据
        return collect($channels)->map(function ($channel) use ($dateRange, $actualData) {
            $channelData = collect($dateRange)->map(function ($date) use ($channel, $actualData) {
                return [
                    'date' => $date,
                    'total_count' => $actualData->get($channel, collect())
                            ->firstWhere('date', $date)?->total_count ?? 0
                ];
            })->sortByDesc('date')->values();

            return [
                'channel_name' => $channel,
                'data' => $channelData
            ];
        })->sortBy('channel_name')->values();
    }
}